NOT Condition

This SQL tutorial explains how to use the SQL NOT condition with syntax and examples.

Description

The SQL NOT Condition (also known as the SQL NOT Operator) is used to negate a condition in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the SQL NOT Condition is:

NOT condition

Parameters or Arguments

condition

This is the condition to negate. The opposite of the condition be must be met for the record to be included in the result set.

Example - Combine With IN condition

The SQL NOT condition can be combined with the IN Condition.

For example:

SELECT *

FROM suppliers

WHERE supplier_name NOT IN ( 'IBM', 'Hewlett Packard', 'Microsoft' );

This SQL NOT example would return all rows where the supplier_name is neither IBM, Hewlett Packard, or Microsoft. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

Example - Combine With IS NULL condition

The SQL NOT condition can also be combined with the IS NULL Condition.

For example,

SELECT *

FROM customers

WHERE customer_name IS NOT NULL;

This SQL NOT example would return all records from the customers table where the customer_name does not contain a NULL value.

Example - Combine With LIKE condition

The SQL NOT condition can also be combined with the LIKE Condition.

For example:

SELECT supplier_name

FROM suppliers

WHERE supplier_name NOT LIKE 'T%';

By placing the SQL NOT Operator in front of the SQL LIKE condition, you are able to retrieve all suppliers whose supplier_name does not start with 'T'.

Example - Combine With BETWEEN condition

The SQL NOT condition can also be combined with the BETWEEN Condition. Here is an example of how you would combine the NOT Operator with the BETWEEN Condition.

For example:

SELECT *

FROM suppliers

WHERE supplier_id NOT BETWEEN 5000 AND 5500;

This SQL NOT example would return all rows where the supplier_id was NOT between 5000 and 5500, inclusive. It would be equivalent to the following SQL SELECT statement:

SELECT *

FROM suppliers

WHERE supplier_id < 5000

OR supplier_id > 5500;

Example - Combine With EXISTS condition

The SQL NOT condition can also be combined with the EXISTS Condition.

For example,

SELECT *

FROM suppliers

WHERE NOT EXISTS (SELECT *

                  FROM orders

                  WHERE suppliers.supplier_id = orders.supplier_id);

This SQL NOT example would return all records from the suppliers table where there are no records in the orders table for the given supplier_id.